﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Atom.DAL.Common;
using System.Data;
using Atom.DataAccess;

namespace Atom.DAL
{
    public class LeaveHosRecordDAL : BaseDAL
    {
        #region 通用

        /// <summary>
        /// 格式化多病种的查询条件
        /// </summary>
        /// <param name="sickname"></param>
        /// <returns></returns>
        public static string formateSickName(string sickname)
        {
            return "'" + sickname.Replace(",", "','") + "'";
        }

        /// <summary>
        /// 模糊查询格式化
        /// </summary>
        /// <param name="sickname"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        private string formateSickName2Like(string sickname, string field)
        {
            //return "'%" + sickname.Replace(",", "','") + "'";

            return "'%" + sickname.Replace(",", "%' OR " + field + " like '%") + "%'";
        }

        #endregion

        #region C1 表


        /*------------------------------------------------------C1表--------------------------------------------------------*/





        /// <summary>
        /// C1表获取（新农合）住院总费用，（药品，检查）费用
        /// </summary>
        /// <param name="dtbegin"></param>
        /// <param name="dtEnd"></param>
        /// <param name="feetype"></param>
        /// <param name="invoiceItemid"></param>
        /// <returns></returns>
        public DataSet GetC1EntityBasic(DateTime dtbegin, DateTime dtEnd, int ageBegin, int ageEnd, string feetype, string invoiceItemid)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 5;
                sql = @" SELECT  COALESCE(SUM(COALESCE(B.TOTALCONSUMEMONEY,0)),0) TOTALCONSUMEMONEY,COALESCE(SUM(COALESCE(A.INFACTMONEY,0)),0)  INFACTMONEY,COUNT(B.INHOSID) INHOSCOUNT,COALESCE(SUM(COALESCE(B.INHOSDAYS,0)),0) INHOSDAYS
                          FROM H_BALANCE_DETAIL A,H_LEAVEHOS_RECORD B ,T_PATIENT_INFO C
                          WHERE A.INHOSID=B.INHOSID AND A.CURRENTINHOSMARK=B.CURRENTINHOSMARK AND B.DIAGNOSEID=C.DIAGNOSEID
                          AND ((C.AGE >=:AGEBEGIN and C.age<=:AGEEND and C.AGEUNIT='岁' )or(C.AGE/12 >=:AGEBEGIN and C.age/12<=:AGEEND and C.AGEUNIT='月' )or (C.AGE/365 >=:AGEBEGIN and C.age/365<=:AGEEND and C.AGEUNIT='天'))
                          AND A.INVOICEITEMID=:INVOICEITEMID 
                          AND LEAVEHOSTRANSACTDATE>=:DTBEGIN AND LEAVEHOSTRANSACTDATE<=:DTEND
                        ";
                if (!string.Empty.Equals(feetype))
                {
                    pars = 6;
                    sql += " AND B.FEETYPEID=:FEETYPEID";
                }
                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                para.Add(":AGEBEGIN", ageBegin);
                para.Add(":AGEEND", ageEnd);
                para.Add(":INVOICEITEMID", invoiceItemid);

                if (!string.Empty.Equals(feetype))
                {
                    para.Add(":FEETYPEID", feetype);
                }

                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// C1获取 总费用
        /// </summary>
        /// <param name="dtbegin"></param>
        /// <param name="dtEnd"></param>
        /// <param name="feetype"></param>
        /// <param name="summary"></param>
        /// <returns></returns>
        public DataSet GetC1EntityTotalFee(DateTime dtbegin, DateTime dtEnd, int ageBegin, int ageEnd, string feetype)
        {
            try
            {
                // COALESCE(SUM(COALESCE(B.TOTALCONSUMEMONEY,0)),0) TOTALCONSUMEMONEY,COALESCE(SUM(COALESCE(A.INFACTMONEY,0)),0)  INFACTMONEY,COUNT(B.INHOSID) INHOSCOUNT,COALESCE(SUM(COALESCE(B.INHOSDAYS,0)),0) INHOSDAYS
                //COALESCE(SUM(COALESCE(T4.TOTALCONSUMEMONEY,0)),0) TOTALCONSUMEMONEY,
                //AND T5.AGE >=:AGEBEGIN AND T5.AGE<=:AGEEND 
                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 5;
                sql = @"  SELECT  COALESCE(SUM(COALESCE(T4.DISCOUNTPAY,0)),0) DISCOUNTPAY
                          FROM  H_LEAVEHOS_RECORD AS T1,H_BALANCE_RECORD AS T4,T_PATIENT_INFO T5
                          WHERE 
                             T1.INHOSID=T4.INHOSID AND T1.CURRENTINHOSMARK =T4.CURRENTINHOSMARK
                          AND T1.DIAGNOSEID=T5.DIAGNOSEID
                          AND T1.FEETYPEID=:FEETYPEID  
                          AND ((T5.AGE >=:AGEBEGIN and T5.age<=:AGEEND and T5.AGEUNIT='岁' )or(T5.AGE/12 >=:AGEBEGIN and T5.age/12<=:AGEEND and T5.AGEUNIT='月' )or (T5.AGE/365 >=:AGEBEGIN and T5.age/365<=:AGEEND and T5.AGEUNIT='天'))
                          AND T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
                          AND T1.LEAVEHOSTRANSACTDATE<=:DTEND
                        ";
             
                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                para.Add(":AGEBEGIN", ageBegin);
                para.Add(":AGEEND", ageEnd);
                para.Add(":FEETYPEID", feetype);


                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// C1获取 总费用， 补偿总费用  （药品，检查）
        /// </summary>
        /// <param name="dtbegin"></param>
        /// <param name="dtEnd"></param>
        /// <param name="feetype"></param>
        /// <param name="summary"></param>
        /// <returns></returns>
        public DataSet GetC1EntityDetail(DateTime dtbegin, DateTime dtEnd, int ageBegin, int ageEnd, string feetype, string summary)
        {
            try
            {
                // COALESCE(SUM(COALESCE(B.TOTALCONSUMEMONEY,0)),0) TOTALCONSUMEMONEY,COALESCE(SUM(COALESCE(A.INFACTMONEY,0)),0)  INFACTMONEY,COUNT(B.INHOSID) INHOSCOUNT,COALESCE(SUM(COALESCE(B.INHOSDAYS,0)),0) INHOSDAYS
                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 5;
                sql = @"  SELECT COALESCE(SUM(COALESCE(T4.TOTALCONSUMEMONEY,0))/COUNT(DISTINCT(T2.ITEMID)),0) TOTALCONSUMEMONEY, COALESCE(SUM(COALESCE(T4.DISCOUNTPAY,0))/COUNT(DISTINCT(T2.ITEMID)),0) DISCOUNTPAY,COALESCE(SUM(COALESCE(T3.MONEY,0)),0) INFACTMONEY
                          FROM  H_LEAVEHOS_RECORD AS T1,D_SUMMARY_INFO AS T2,
                                H_LEAVEHOSCHARGE_DETAIL AS T3,H_BALANCE_RECORD AS T4,T_PATIENT_INFO T5
                          WHERE 
                             T1.INHOSID=T4.INHOSID AND T1.CURRENTINHOSMARK =T4.CURRENTINHOSMARK
                          AND T1.INHOSID=T3.INHOSID AND T1.CURRENTINHOSMARK =T3.CURRENTINHOSMARK AND T1.DIAGNOSEID=T5.DIAGNOSEID
                          AND T3.ITEMID=T2.ITEMID AND T1.FEETYPEID=:FEETYPEID  
                          AND ((T5.AGE >=:AGEBEGIN and T5.age<=:AGEEND and T5.AGEUNIT='岁' )or(T5.AGE/12 >=:AGEBEGIN and T5.age/12<=:AGEEND and T5.AGEUNIT='月' )or (T5.AGE/365 >=:AGEBEGIN and T5.age/365<=:AGEEND and T5.AGEUNIT='天'))
                          AND T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
                          AND T1.LEAVEHOSTRANSACTDATE<=:DTEND
                        ";
                if (!string.Empty.Equals(summary))
                {
                    sql += " AND T2.SUMMARY in ( " + summary + ")";
                }
                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                para.Add(":AGEBEGIN", ageBegin);
                para.Add(":AGEEND", ageEnd);
                para.Add(":FEETYPEID", feetype);

              
                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        #endregion

        #region C2 表

        /*------------------------------------------------------C2表--------------------------------------------------------*/
        /// <summary>
        /// 根据起止时间获取门诊费用详细信息
        /// </summary>
        /// <param name="dtbegin">开始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="itemid">项目类别</param>
        /// <param name="where">附件查询条件</param>
        /// <returns></returns>
        public DataSet GetC2EntityDetail(DateTime dtbegin, DateTime dtEnd, string itemid, string where)
        {
            try
            {
                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;
                sql = @"SELECT COALESCE(SUM(COALESCE(MONEY,0)),0) MONEY, COALESCE(SUM(COALESCE(DISCOUNTDEFRAY,0)),0) DISCOUNTDEFRAY,COUNT(DISTINCT DIAGNOSEID) DIAGNOSEID FROM D_DETAIL_ACCOUNT 
                        WHERE OPERATEDATE>=:DTBEGIN AND OPERATEDATE<=:DTEND
                        ";
                if (!string.Empty.Equals(itemid))
                {
                    pars = 3;
                    sql += " AND ITEMID=:ITEMID";
                }
                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);



                if (!string.Empty.Equals(itemid))
                {
                    para.Add(":ITEMID", itemid);
                }

                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// 获取补偿人次数
        /// </summary>
        /// <param name="dtbegin">开始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="where">附件条件</param>
        /// <returns></returns>
        public DataSet GetC2EntityBCCount(DateTime dtbegin, DateTime dtEnd, string where)
        {
            try
            {
                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;
                sql = @"  SELECT COUNT(INHOSID) INHOSID
                          FROM H_LEAVEHOS_RECORD 
                          WHERE SICKTYPEID IN('0017','0003','0016')
                          AND LEAVEHOSTRANSACTDATE>=:DTBEGIN
                          AND LEAVEHOSTRANSACTDATE<=:DTEND
                        ";

                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);

                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        #endregion

        #region C3_1 表

        /// <summary>
        /// 根据条件获取具体某项的费用
        /// </summary>
        /// <param name="dtbegin">开始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="ageBegin">开始年龄</param>
        /// <param name="ageEnd">结束年龄</param>
        /// <param name="sickName">疾病集合</param>
        /// <returns></returns>
        public DataSet GetLeaveHosRecordByDateAge(DateTime dtbegin, DateTime dtEnd, int ageBegin, int ageEnd, string sickName, string medType)
        {


            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 5;
                sql = @"SELECT  count(T1.INHOSID)REGISTERNO,COALESCE(SUM(COALESCE(T2.INHOSDAYS,0)),0)INHOSDAYS,COALESCE(SUM(COALESCE(T1.TOTALCONSUMEMONEY,0)),0) TOTALCONSUMEMONEY, COALESCE(SUM(COALESCE(T2.DISCOUNTPAY,0)),0) DISCOUNTPAY
                          FROM 
                          H_LEAVEHOS_RECORD T1 ,H_BALANCE_RECORD T2,S_SICKNESSCODE AS T3,T_PATIENT_INFO T4
                          WHERE
                          (T1.INHOSID=T2.INHOSID AND T1.CURRENTINHOSMARK=T2.CURRENTINHOSMARK)
                          AND T1.ENTERHOSDIAGNOSEID=T3.SICKNESSID AND  T1.DIAGNOSEID=T4.DIAGNOSEID
                          AND ((T4.AGE >=:AGEBEGIN and T4.age<=:AGEEND and T4.AGEUNIT='岁' )or(T4.AGE/12 >=:AGEBEGIN and T4.age/12<=:AGEEND and T4.AGEUNIT='月' )or (T4.AGE/365 >=:AGEBEGIN and T4.age/365<=:AGEEND and T4.AGEUNIT='天'))
                          AND  T3.SICKNESSID in(" + formateSickName(sickName) + @") 
                          and T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
                          and T1.LEAVEHOSTRANSACTDATE<=:DTEND 
                        ";//AND T3.MEDTYPE !=:MEDTYPE

                if (!string.Empty.Equals(medType))
                {
                    pars = 6;
                    sql += " AND T1.FEETYPEID=:FEETYPEID";
                }
                para = new QueryParameterCollection(pars);

                para.Add(":AGEBEGIN", ageBegin);
                para.Add(":AGEEND", ageEnd);

                //para.Add(":MEDTYPE", medType);
                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                if (!string.Empty.Equals(medType))
                {
                    para.Add(":FEETYPEID", medType);

                }
                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// 根据条件获取具体某项的费用
        /// </summary>
        /// <param name="dtbegin">开始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="ageBegin">开始年龄</param>
        /// <param name="ageEnd">结束年龄</param>
        /// <param name="sickName">疾病集合</param>
        /// <param name="summary">费用类别</param>
        /// <returns></returns>
        public DataSet GetLeaveHosRecordByDateAgeForItem(DateTime dtbegin, DateTime dtEnd, int ageBegin, int ageEnd, string sickName, string summary, string medType)
        {


            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                //                sql = @"SELECT COALESCE(SUM(COALESCE(AA2.MONEY,0)),0) MONEY FROM (
                //                          SELECT T1.INHOSID,T1.CURRENTINHOSMARK
                //                          FROM 
                //                          H_LEAVEHOS_RECORD T1 ,H_BALANCE_RECORD T2,S_SICKNESSCODE AS T3,T_PATIENT_INFO T4
                //                          WHERE
                //                          (T1.INHOSID=T2.INHOSID AND T1.CURRENTINHOSMARK=T2.CURRENTINHOSMARK)
                //                          AND T1.ENTERHOSDIAGNOSEID=T3.SICKNESSID AND  T1.DIAGNOSEID=T4.DIAGNOSEID
                //                          AND T4.AGE >=:AGEBEGIN and T4.age<=:AGEEND and T4.AGEUNIT='岁'
                //                          AND  T3.SICKNESSNAME IN(" + formateSickName(sickName) + @") AND T3.MEDTYPE=:MEDTYPE
                //                          and T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
                //                          and T1.LEAVEHOSTRANSACTDATE<=:DTEND) AA1,H_LEAVEHOSCHARGE_DETAIL AA2,D_SUMMARY_INFO AA3
                //                          WHERE (AA2.INHOSID=AA1.INHOSID AND AA2.CURRENTINHOSMARK=AA1.CURRENTINHOSMARK)
                //                          AND AA2.ITEMID=AA3.ITEMID AND AA3.SUMMARY=:SUMMARY
                //                        ";
                sql = @"SELECT COALESCE(SUM(COALESCE(AA2.MONEY,0)),0) MONEY FROM (
	                          SELECT T1.INHOSID,T1.CURRENTINHOSMARK
	                          FROM 
	                          H_LEAVEHOS_RECORD T1 ,S_SICKNESSCODE AS T3,T_PATIENT_INFO T4
	                          WHERE T1.ENTERHOSDIAGNOSEID=T3.SICKNESSID AND  T1.DIAGNOSEID=T4.DIAGNOSEID
	                          AND ((T4.AGE >=:AGEBEGIN and T4.age<=:AGEEND and T4.AGEUNIT='岁' )or(T4.AGE/12 >=:AGEBEGIN and T4.age/12<=:AGEEND and T4.AGEUNIT='月' )or (T4.AGE/365 >=:AGEBEGIN and T4.age/365<=:AGEEND and T4.AGEUNIT='天'))
	                          AND  T3.SICKNESSID IN(" + formateSickName(sickName) + @") AND T1.feetypeid=:MEDTYPE
	                          and T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
	                          and T1.LEAVEHOSTRANSACTDATE<=:DTEND
                          ) AA1,H_LEAVEHOSCHARGE_DETAIL AA2,D_SUMMARY_INFO AA3
                          WHERE (AA2.INHOSID=AA1.INHOSID AND AA2.CURRENTINHOSMARK=AA1.CURRENTINHOSMARK)
                          AND AA2.ITEMID=AA3.ITEMID AND AA3.SUMMARY IN(" + summary + @")";


                para = new QueryParameterCollection(5);
                para.Add(":AGEBEGIN", ageBegin);
                para.Add(":AGEEND", ageEnd);
                // para.Add(":SICKNAME", sickName);
                para.Add(":MEDTYPE", medType);
                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                //para.Add(":SUMMARY", summary);
                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        #endregion

        #region C3_2 表



        /*------------------------------------------------------C3-2表--------------------------------------------------------*/


        /// <summary>
        /// 获取C3_1基本信息
        /// </summary>
        /// <param name="dtbegin"></param>
        /// <param name="dtEnd"></param>
        /// <param name="medicareno"></param>
        /// <param name="sickName"></param>
        /// <returns></returns>
        public DataSet GetC3_2RecordBaseInfo(DateTime dtbegin, DateTime dtEnd, string medicareno, string sickName)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 3;
                sql = @"SELECT TTT1.*,T3.MEDICARESAFETYID, T3.PATIENTNAME,T3.SEX,T3.AGE
                        FROM (SELECT TT1.*, TT2.PERSONALMONEY
                              FROM 
                                 (SELECT  T1.CASEID,T2.MEDICARENO, T1.TOTALCONSUMEMONEY,T1.INHOSDAYS,T1.INHOSID,T1.CURRENTINHOSMARK,T1.DIAGNOSEID
                                  FROM  H_LEAVEHOS_RECORD T1,I_BALANCE_RECORD  T2     
                                  WHERE T1.INHOSID=T2.REGISTERNO AND T1.LEAVEHOSTRANSACTDATE>=:DTBEGIN
                                        AND T1.LEAVEHOSTRANSACTDATE<=:DTEND
                                        AND (T2.SICKNAME like " + formateSickName2Like(sickName, "T2.SICKNAME") + @")
                                  ) TT1,H_BALANCE_RECORD TT2
                              WHERE  TT1.INHOSID=TT2.INHOSID AND TT1.CURRENTINHOSMARK=TT2.CURRENTINHOSMARK  AND OPERATETYPE=3	                     
                              )TTT1,T_PATIENT_INFO T3
                        WHERE  TTT1.DIAGNOSEID=T3.DIAGNOSEID
                        ";

                para = new QueryParameterCollection(pars);

                para.Add(":DTBEGIN", dtbegin);
                para.Add(":DTEND", dtEnd);
                para.Add(":SICKNAME", sickName);



                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        //        public DataSet GetC3_2RecordYPInfo(string inhosid, int currentinhosmark, string tradename)
        //        {
        //            try
        //            {

        //                string sql = "";
        //                QueryParameterCollection para;
        //                DataSet ds = new DataSet();
        //                int pars = 3;
        //                sql = @" SELECT TRADENAME, MONEY 
        //                         FROM 
        //                              H_LEAVEHOSLEECHDOM_DETAIL AS T1,T_CODEX_DETAIL AS T2
        //                         where
        //                           T1.LEECHDOMNO=T2.LEECHDOMDETAILNO
        //                          and T2.TRADENAME in(:TRADENAME)
        //                            AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
        //                        ";

        //                para = new QueryParameterCollection(pars);

        //                para.Add(":INHOSID", inhosid);
        //                para.Add(":CURRENTINHOSMARK", currentinhosmark);
        //                para.Add(":TRADENAME", tradename);


        //                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
        //                return ds;

        //            }
        //            catch (Exception e)
        //            {
        //                throw new Exception(e.Message, e);
        //            }
        //        }

        /// <summary>
        /// 获取药品总费用
        /// </summary>
        /// <param name="inhosid"></param>
        /// <param name="currentinhosmark"></param>
        /// <param name="tradename"></param>
        /// <returns></returns>
        public DataSet GetC3_2RecordYPInfo(string inhosid, int currentinhosmark)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;

                sql = @"select COALESCE(SUM(COALESCE(MONEY,0)),0) MONEY
                        FROM H_LEAVEHOSCHARGE_DETAIL 
                        where INHOSID=:INHOSID and CURRENTINHOSMARK=:CURRENTINHOSMARK
                        and ITEMID in('01','02','03')";
                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);



                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        #region 抗生素 激素 营养类药品费用统计

        /// <summary>
        /// 获取药品信息使用情况 《抗生素》
        /// </summary>
        /// <param name="inhosid"></param>
        /// <param name="currentinhosmark"></param>
        /// <param name="tradename"></param>
        /// <returns></returns>
        public DataSet GetC3_2RecordYPKSInfo(string inhosid, int currentinhosmark, string tradename)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;
                //                sql = @" SELECT TRADENAME, MONEY 
                //                         FROM 
                //                              H_LEAVEHOSLEECHDOM_DETAIL AS T1,T_CODEX_DETAIL AS T2
                //                         where
                //                           T1.LEECHDOMNO=T2.LEECHDOMDETAILNO
                //                          and T2.TRADENAME in(:TRADENAME)
                //                            AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
                //                        ";
                //c.RETAILPRICE

                sql = @"select  CURRENCYNAME TRADENAME,COALESCE(SUM(COALESCE(RETAILPRICE,0)),0) MONEY
                        from P_LEAVEHOSSENDLEECHDOM as a,t_codex as b,T_CODEX_DETAIL as c
                        where a.INHOSID=:INHOSID and a.CURRENTINHOSMARK=:CURRENTINHOSMARK
                        and a.LEECHDOMNO=c.LEECHDOMDETAILNO
                        and c.LEECHDOMNO=b.LEECHDOMNO
                        and (b.DRUGTRUTHACTION like " + formateSickName2Like(tradename, "b.DRUGTRUTHACTION") + ")GROUP BY CURRENCYNAME";

                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);



                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// 获取药品信息使用情况 《激素》
        /// </summary>
        /// <param name="inhosid"></param>
        /// <param name="currentinhosmark"></param>
        /// <param name="tradename"></param>
        /// <returns></returns>
        public DataSet GetC3_2RecordYPJSInfo(string inhosid, int currentinhosmark, string tradename)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;
                //                sql = @" SELECT TRADENAME, MONEY 
                //                         FROM 
                //                              H_LEAVEHOSLEECHDOM_DETAIL AS T1,T_CODEX_DETAIL AS T2
                //                         where
                //                           T1.LEECHDOMNO=T2.LEECHDOMDETAILNO
                //                          and T2.TRADENAME in(:TRADENAME)
                //                            AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
                //                        ";

                sql = @"select  CURRENCYNAME TRADENAME,COALESCE(SUM(COALESCE(RETAILPRICE,0)),0) MONEY
                        from P_LEAVEHOSSENDLEECHDOM as a,t_codex as b,T_CODEX_DETAIL as c
                        where a.INHOSID=:INHOSID and a.CURRENTINHOSMARK=:CURRENTINHOSMARK
                        and a.LEECHDOMNO=c.LEECHDOMDETAILNO
                        and c.LEECHDOMNO=b.LEECHDOMNO
                        and (b.LEECHDOMUSE like " + formateSickName2Like(tradename, "b.LEECHDOMUSE") + ")GROUP BY CURRENCYNAME";
                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);



                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        /// <summary>
        /// 获取药品信息使用情况 《营养类》
        /// </summary>
        /// <param name="inhosid"></param>
        /// <param name="currentinhosmark"></param>
        /// <param name="tradename"></param>
        /// <returns></returns>
        public DataSet GetC3_2RecordYPYYInfo(string inhosid, int currentinhosmark, string tradename)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 2;
                //                sql = @" SELECT TRADENAME, MONEY 
                //                         FROM 
                //                              H_LEAVEHOSLEECHDOM_DETAIL AS T1,T_CODEX_DETAIL AS T2
                //                         where
                //                           T1.LEECHDOMNO=T2.LEECHDOMDETAILNO
                //                          and T2.TRADENAME in(:TRADENAME)
                //                            AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
                //                        ";

                //                sql = @"select  CURRENCYNAME TRADENAME,RETAILPRICE MONEY
                //                        from P_LEAVEHOSSENDLEECHDOM as a,t_codex as b,T_CODEX_DETAIL as c
                //                        where a.INHOSID=:INHOSID and a.CURRENTINHOSMARK=:CURRENTINHOSMARK
                //                        and a.LEECHDOMNO=c.LEECHDOMDETAILNO
                //                        and c.LEECHDOMNO=b.LEECHDOMNO
                //                        and b.LEECHDOMUSE in (" + formateSickName(tradename) + ")";

                sql = @"select b.CURRENCYNAME TRADENAME,COALESCE(SUM(COALESCE(c.RETAILPRICE,0)),0) MONEY
                        from P_LEAVEHOSSENDLEECHDOM as a,t_codex as b,T_CODEX_DETAIL as c
                        where a.INHOSID=:INHOSID and a.CURRENTINHOSMARK=:CURRENTINHOSMARK
                        and a.LEECHDOMNO=c.LEECHDOMDETAILNO
                        and c.LEECHDOMNO=b.LEECHDOMNO
                        and c. LEECHDOMNO in(" + formateSickName(tradename) + ")GROUP BY b.CURRENCYNAME";
                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);



                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        #endregion

        public DataSet GetC3_2RecordZLInfo(string inhosid, int currentinhosmark, string tradename)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 3;
                sql = @" SELECT CHARGEITEM, COALESCE(SUM(COALESCE(MONEY,0)),0) MONEY,COUNT(CHARGEITEM) CHARGECOUNT
                         FROM 
                              H_LEAVEHOSCHARGE_DETAIL  AS T1,D_SUMMARY_INFO AS T2
                         WHERE
                           T1.ITEMID=T2.ITEMID
                          AND T2.CHARGEITEM IN(:CHARGEITEM)
                          AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
                        GROUP BY T2.CHARGEITEM
                        ";

                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);
                para.Add(":CHARGEITEM", tradename);


                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        public DataSet GetC3_2RecordZLBaseInfo(string inhosid, int currentinhosmark, string summary)
        {
            try
            {

                string sql = "";
                QueryParameterCollection para;
                DataSet ds = new DataSet();
                int pars = 3;
                sql = @" SELECT CHARGEITEM, COALESCE(SUM(COALESCE(MONEY,0)),0) MONEY
                         FROM 
                              H_LEAVEHOSCHARGE_DETAIL  AS T1,D_SUMMARY_INFO AS T2
                         where
                           T1.ITEMID=T2.ITEMID
                            AND  T1.INHOSID=:INHOSID AND T1.CURRENTINHOSMARK=:CURRENTINHOSMARK
                            AND T2.SUMMARY=:SUMMARY
                        group by T2.CHARGEITEM
                        ";

                para = new QueryParameterCollection(pars);

                para.Add(":INHOSID", inhosid);
                para.Add(":CURRENTINHOSMARK", currentinhosmark);
                para.Add(":SUMMARY", summary);


                DataAccess.ExecuteDataset(sql, para, ds, "H_LEAVEHOS_RECORD");
                return ds;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }


        #endregion


    }
}
